﻿CREATE PROCEDURE [acms].[Site_GetByUrl]
	@Url varchar (255)
AS
	declare @Id int
	declare @RootUrl varchar (255)

	set @RootUrl = @Url

	if(Right(@RootUrl, 1) = '/')
		set @RootUrl = SubString(@RootUrl, 1, Len(@RootUrl) - 1)
	
	while Len(@RootUrl) > 0 and @Id is null begin
	
		SELECT @Id = Id
		FROM [acms].[Site_View]
		WHERE Root = @RootUrl
		
		if(@Id is null) begin
			SELECT @Id = SiteId
			FROM [acms].[SiteMappings]
			WHERE MappingName = @RootUrl
		end

		declare @SlashIndex int
		set @SlashIndex = CharIndex('/', Reverse(@RootUrl))
		if @SlashIndex = 0
			set @RootUrl = ''
		else
			set @RootUrl= SubString(@RootUrl, 1, Len(@RootUrl) - @SlashIndex)
	end
	
	Select * From [acms].[Site_View] where Id = @Id
RETURN 0;